package dao.impl;

import dao.flowerMapper;
import domain.customer;
import domain.flower;
import domain.flowerstore;
import domain.order;
import utill.DateUtils;
import utill.JDBCUtil;
import utill.ParamUtil;
import utill.ResultUtil;
import java.sql.*;
import java.util.List;
import execption.*;


public class flowerMapperImpl implements flowerMapper {
    /**
     * 该商店的所有鲜花查询所有
     * @param flag      判断是顾客(false)还是商家查询(true);
     * @return 鲜花集合;
     */

    @Override
    public List<flower> findFlowers(boolean flag) {
        ResultSet rs =null;
        List<flower> flowerList=null;
        Statement st=null;
        Connection connection=null;
        Integer storeId=ParamUtil.storeId.get(Thread.currentThread().getName()+"storeId");
        String sql="";

        if(flag){
            sql="select * from flower where  storeId="+storeId;
        }else{
            sql="select * from  flower";
        }
        try{
            connection = JDBCUtil.getConn();
            st=connection.createStatement();
            rs =st.executeQuery(sql);
            flowerList=ResultUtil.PackingFlower(rs);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(connection,st,rs);
        }

        return flowerList;
    }

    /**
     *鲜花入库
     * @param f
     * @return 返回跟新成功行数
     * @throws SQLException
     * @throws nullValueException
     */
    @Override
    public int updateFlower(flower f) throws SQLException, nullValueException {
        PreparedStatement ps=null;
        Connection connection=null;
        List<flower> flowerList=null;
        ResultSet rs =null;
        int n=0;
        try{
            connection=JDBCUtil.getConn();
            connection.setAutoCommit(false);
            String sql1="select * from flower where flowerId=?";
            String sql2 ="UPDATE flower set flowertotal=? WHERE flowerId=?";
            ps=connection.prepareStatement(sql1);
            ps.setInt(1,f.getFlowerId());
            rs=ps.executeQuery();

            flowerList=ResultUtil.PackingFlower(rs);
            if(flowerList.size()==0){
                throw  new nullValueException("非法值");
            }
            int number=flowerList.get(0).getFlowertotal();

            System.out.println(number);
            ps =connection.prepareStatement(sql2);
            number = number-f.getFlowertotal()>=0 ?  number-f.getFlowertotal() : 0;
            ps.setInt(1,number);
            ps.setInt(2,f.getFlowerId());
            n= ps.executeUpdate();
            connection.commit();
        }catch (SQLException e){
            connection.rollback();
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(ps ,rs,connection);
        }

        return n;
    }

    /**
     * 鲜花入库
     * @param f
     * @param flag
     * @return  返回跟新成功行数
     * @throws SQLException
     * @throws nullValueException
     */
    @Override
    public int updateFlower(flower f, boolean flag) throws SQLException, nullValueException {
        PreparedStatement ps=null;
        Connection connection=null;
        List<flower> flowerList=null;
        ResultSet rs =null;
        int n=0;
        try{
            connection=JDBCUtil.getConn();
            connection.setAutoCommit(false);
            String sql1="select * from flower where flowerId=?";
            String sql2 ="UPDATE flower set flowertotal=? WHERE flowerId=?";
            ps=connection.prepareStatement(sql1);
            ps.setInt(1,f.getFlowerId());
            rs=ps.executeQuery();

            flowerList=ResultUtil.PackingFlower(rs);
            if(flowerList.size()==0){
                throw  new nullValueException("非法值");
            }
            int number=flowerList.get(0).getFlowertotal();

            System.out.println(number);
            ps =connection.prepareStatement(sql2);
            number =  number >=0 ? number+f.getFlowertotal() : f.getFlowertotal();
            ps.setInt(1,number);
            ps.setInt(2,f.getFlowerId());
            n= ps.executeUpdate();
            connection.commit();
        }catch (SQLException e){
            connection.rollback();
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(ps ,rs,connection);
        }

        return n;

    }

    /**
     * 向库存里添加新种类的鲜花
     * @param f
     * @return 返回插入成功行数
     * @throws repeatValueException
     */

    @Override
    public int addFlower(flower f) throws repeatValueException {
        PreparedStatement ps =null;
        Connection connection =null;
        List<flower> flowerList=null;
        ResultSet rs =null;
        int n=0;
        try{
               connection = JDBCUtil.getConn();
               String sql1="select * from flower where flowerType=?";
               String sql2="insert into flower(flowerType ,flowerPrice ,flowertotal , storeId) values(?,?,? ,?)";
               ps=connection.prepareStatement(sql1);
               ps.setString(1,f.getFlowerType());
               rs =ps.executeQuery();
               flowerList=ResultUtil.PackingFlower(rs);
               if(flowerList.size()!=0) {
                    throw  new repeatValueException("类型重复");
               }
               ps=connection.prepareStatement(sql2);
               ps.setString(1,f.getFlowerType());
               ps.setString(2,f.getFlowerPrice());
               ps.setInt(3,f.getFlowertotal());
               ps.setInt(4,f.getStoreId());

               n=ps.executeUpdate();


        }catch (SQLException e){
                e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(ps,rs,connection);
        }


        return n;
    }

    /**
     * 通过flowerId 和 flowerPrice 查找鲜花集合
     * @param flowerId
     * @param flowerTotal
     * @param flowerPrice
     * @return  返回鲜花集合
     */
    @Override
    public List<flower> findFlowerByIdAndTypeAndPrice(String flowerId, String flowerTotal, String flowerPrice) {
        ResultSet rs =null;
        List<flower> flowerList=null;
        PreparedStatement ps=null;
        Connection connection=null;
        int upperBound=0;
        int downBound=20;
        int id=0;
        if(!"".equals(flowerPrice)  && !" ".equals(flowerPrice)){
            int number=Integer.valueOf(flowerPrice);
            upperBound+=number;
            downBound+=number;
        }

        if(!"".equals(flowerId) && !" ".equals(flowerId)){
            id=Integer.valueOf(flowerId);
        }


        try {
            connection=JDBCUtil.getConn();
            String sql="select * from flower where flowerId=? or flowertotal = ? or flowerPrice  BETWEEN ? and ?";
            ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ps.setString(2,flowerTotal);
            ps.setInt(3,upperBound);
            ps.setInt(4,downBound);
            rs=ps.executeQuery();
            flowerList=ResultUtil.PackingFlower(rs);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(ps,rs,connection);
        }
        return flowerList;
    }

    /**
     * 购买鲜花
     * @param f
     * @return  返回插入记录行数;
     * @throws lackGoodException
     * @throws nullValueException
     */

    @Override
    public int buyFlowers(order f) throws lackGoodException, nullValueException {
        ResultSet rs =null;
        PreparedStatement ps=null;
        Connection connection=null;
        List<flower>  flowerList =null;
        List<customer> customerList=null;
        List<flowerstore> flowerstoreList=null;
        int  n=0;


        try{
            connection=JDBCUtil.getConn();
            connection.setAutoCommit(false);
            String  sql1="select * from flower where  flowerType=? ";
            String  sql2="insert into  `order`(customerId,storeId , flowertype, flowernumber, totalprice , orderdate)   values(? ,? ,? ,? ,?, ?)";
            String  sql3="update flower set flowertotal=? where flowerId=?";
            String  sql4="update  flowerstore set storeIncome=? WHERE  storeId=?";
            String  sql5 ="UPDATE  customer set customerMoney=? WHERE  customerId=?";
            String  sql6="select *  FROM customer WHERE customerId=?";
            String  sql7="SELECT * FROM flowerstore WHERE storeId=?";
            Integer userId =ParamUtil.userId.get(Thread.currentThread().getName()+"userId");


            //查询是否存在该种类的鲜花;
            ps=connection.prepareStatement(sql1);
            ps.setString(1, f.getFlowertype());
            rs=ps.executeQuery();
            flowerList=ResultUtil.PackingFlower(rs);
            if(flowerList.size()==0) {
                throw new  nullValueException("没有该类型的花");
            }
            if(flowerList.get(0).getFlowertotal()<f.getFlowernumber()){
                throw  new lackGoodException("库存不足");
            }

            //查询顾客余额是否充足;
            ps=connection.prepareStatement(sql6);
            ps.setInt(1,userId);
            rs=ps.executeQuery();
            customerList=ResultUtil.PackingCustomer(rs);

            int sumPrice=Integer.valueOf(flowerList.get(0).getFlowerPrice())*Integer.valueOf(f.getFlowernumber());


            if((Integer.valueOf(customerList.get(0).getCustomerMoney())-sumPrice)<0){
                  throw   new nullValueException("您的金额不足，请冲值");
            }

            //查询商家收入;
            ps=connection.prepareStatement(sql7);
            ps.setInt(1,flowerList.get(0).getStoreId());
            rs=ps.executeQuery();
            flowerstoreList=ResultUtil.PackingFlowerStore(rs);


            String totalPrice=String.valueOf(sumPrice);

            //添加订单;
            ps=connection.prepareStatement(sql2);
            ps.setInt( 1 , userId);
            ps.setInt(2, f.getStoreId());
            ps.setString(3,f.getFlowertype());
            ps.setInt(4,f.getFlowernumber());
            ps.setString(5,totalPrice);
            ps.setString(6, DateUtils.getTime());
            n=ps.executeUpdate();
            ps=connection.prepareStatement(sql3);
            ps.setInt(1,flowerList.get(0).getFlowertotal()-f.getFlowernumber());
            ps.setInt(2,flowerList.get(0).getFlowerId());
            int x=ps.executeUpdate();
            //更改顾客消费金额;
            ps=connection.prepareStatement(sql5);
            ps.setString(1,  String.valueOf(Integer.valueOf(customerList.get(0).getCustomerMoney())-sumPrice));
            ps.setInt(2,userId);
            int y=ps.executeUpdate();

            //更改鲜花店收入;
            ps=connection.prepareStatement(sql4);
            ps.setString(1,String.valueOf(Integer.valueOf(flowerstoreList.get(0).getStoreIncome())+sumPrice));
            ps.setInt(2,flowerList.get(0).getStoreId());
            int z=ps.executeUpdate();

            connection.commit();

        }catch (SQLException e){
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(ps,rs,connection);
        }

        return n;
    }

}
